package blog;

import javax.management.Query;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class ArticleListAction implements Action {
    @Override
    public void run() {
        try (Connection connection = DBUtil.getConnection()) {
            List<String[]> articleList = new ArrayList<>();
            String sql = "select id,author_id,title,published_at from articles order by published_at desc";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    while (resultSet.next()) {
                        String[] article = new String[4];
                        String id = resultSet.getString("id");
                        String authorId = resultSet.getString("author_id");
                        String title = resultSet.getString("title");
                        String publishedAt = resultSet.getString("published_at");
                        article[0] = id;
                        article[1] = authorId;
                        article[2] = title;
                        article[3] = publishedAt;

                        articleList.add(article);
                    }
                }
            }

            //TODO：打印作者的昵称而不是id信息
            //需要根据作者id,再次去users表中查出用户的昵称信息

            //提取出所有需要查询的作者的id
            //利用Set中不会存在重复的元素的特征
            Set<String> authorIdSet = new HashSet<>();
            for (String[] article : articleList) {
                String authorId = article[1];
                authorIdSet.add(authorId);
            }

            //提取的作者id，拼接出一个根据id查询作者昵称的sql
            //select id,nickname from users where id in (...)
            //占位符需要的个数也不清楚，所以需要动态生成
            StringBuilder QuerySql = new StringBuilder("select id,nickname from users where id in (");
            for (int i = 0; i < authorIdSet.size(); i++) {
                QuerySql.append("?,");
            }
            QuerySql.append("?)");
            System.out.println("DEBUG" + QuerySql.toString());

            Map<String, String> userIdToNicknameMap = new HashMap<>();
            try (PreparedStatement statement = connection.prepareStatement(QuerySql.toString())) {
                int i = 1;
                for (String id : authorIdSet) {
                    statement.setString(i++, id);
                }
                try (ResultSet resultSet = statement.executeQuery()) {
                    while (resultSet.next()) {
                        String id = resultSet.getString("id");
                        String nickname = resultSet.getString("nickname");
                        userIdToNicknameMap.put(id, nickname);
                    }
                }
            }

            System.out.printf("#ID | 标题 | 作者 | 发表时间%n");
            for (String[] article : articleList) {
                String id = article[0];
                String authorId = article[1];
                String authorNickname = userIdToNicknameMap.get(authorId);
                String title = article[2];
                String publishedAt = article[3];
                System.out.printf("%4s | %-10s | %-10s |%s%n", id, title, authorNickname, publishedAt);
            }
        } catch (SQLException e) {
            System.out.println("错误" + e.getMessage());
        }
    }
}
